package org.lq.recruitstudent.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.lq.recruitstudent.dao.AuditionInfoDao;
import org.lq.recruitstudent.entity.AuditionInfo;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author 冯志远
 * @create 2020-10-13 19:08
 */
@Log4j
public class AuditionInfoDaoImpl implements AuditionInfoDao {
    @Override
    public int save(AuditionInfo auditionInfo) {
        log.info("数据访问层==[AuditionInfoDaoImpl]->save(AuditionInfo auditionInfo),开始执行");
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num= qr.update("insert into audition_info(Student_id,audition_time, audition_addr, audition_course, audition_desc) values (?,?,?,?,?)",
                    auditionInfo.getStudentId(),
                    auditionInfo.getAuditionTime(),
                    auditionInfo.getAuditionAddr(),
                    auditionInfo.getAuditionCourse(),
                    auditionInfo.getAuditionDesc());
        } catch (SQLException e) {
            log.error("添加数据出现异常",e);
        }
        log.info("返回结果"+num);
        log.info("数据访问层==[AuditionInfoDaoImpl]->save(AuditionInfo auditionInfo),执行结束");
        return num;
    }

    @Override
    public int update(AuditionInfo auditionInfo) {
        log.info("数据访问层==[AuditionInfoDaoImpl]->update(AuditionInfo auditionInfo),开始执行");
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            System.out.println("--------");
            num = qr.update("update audition_info set student_id=?, audition_time=? ,audition_addr=? , audition_course=? , audition_desc=? where audition_id=?"
                    ,auditionInfo.getStudentId(),auditionInfo.getAuditionTime(),
                    auditionInfo.getAuditionAddr(),auditionInfo.getAuditionCourse(),auditionInfo.getAuditionDesc(),auditionInfo.getAuditionId());
        } catch (SQLException e) {
            log.error("修改数据出现异常",e);
        }
        log.info("返回结果"+num);
        log.info("数据访问层==[AuditionInfoDaoImpl]->update(AuditionInfo auditionInfo),执行结束");

        return num;
    }

    @Override
    public int delete(int id) {
        log.info("数据访问层==[AuditionInfoDaoImpl]->delete(int id),开始执行");
        int num = 0;
        try {
            num = new QueryRunner(JDBCUtil.getDataSource()).update("delete from audition_info where audition_id=?",id);
        } catch (SQLException throwables) {
            log.error("删除数据出现异常",throwables);
        }
        log.info("返回结果"+num);
        log.info("数据访问层==[AuditionInfoDaoImpl]->delete(int id),执行结束");

        return num;
    }

    @Override
    public AuditionInfo getById(int id) {
        log.info("数据访问层==[AuditionInfoDaoImpl]->getById(int id),开始执行");
        AuditionInfo auditionInfo = null;
        try {
            auditionInfo =  new QueryRunner(JDBCUtil.getDataSource()).query("select * from v_audition_info where auditionId=?",
                    new BeanHandler<AuditionInfo>(AuditionInfo.class),id);
        } catch (SQLException throwables) {
            log.error("根据ID获取数据出现异常",throwables);

        }
        log.info("返回结果"+auditionInfo.toString());
        log.info("数据访问层==[AuditionInfoDaoImpl]->getById(int id),执行结束");
        return auditionInfo;
    }

    @Override
    public int getCount() {
        log.info("数据访问层==[AuditionInfoDaoImpl]->getCount(),开始执行");

        int count = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            Long num = qr.query("select count(1) from v_audition_info",new ScalarHandler<Long>());
            count = num.intValue();
        } catch (SQLException throwables) {
            log.error("获取数据数量出现异常",throwables);

        }
        log.info("返回结果"+count);
        log.info("数据访问层==[AuditionInfoDaoImpl]->getCount(),执行结束");
        return count;
    }

    @Override
    public List<AuditionInfo> pageList(int startIndex, int pageSize) {
        log.info("数据访问层==[AuditionInfoDaoImpl]->pageList(int startIndex, int pageSize),开始执行");

        List<AuditionInfo> list = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = qr.query("select * from v_audition_info limit ?,?",new BeanListHandler<AuditionInfo>(AuditionInfo.class),startIndex,pageSize);
        } catch (SQLException throwables) {
            log.error("获取分页数据出现异常",throwables);

        }
        log.info("返回结果"+list.toString());
        log.info("数据访问层==[AuditionInfoDaoImpl]->pageList(int startIndex, int pageSize),执行结束");
        return list;
    }

    @Override
    public int getCount(String... values) {
        log.info("数据访问层==[AuditionInfoDaoImpl]->getCount(String... values),开始执行");

        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
//               num =  queryRunner.query("SELECT count(1) FROM v_audition_info,v_student_info WHERE v_student_info.studentName LIKE ? AND v_student_info.studentId=v_audition_info.studentId AND v_audition_info.auditionCourse like ? ",new ScalarHandler<Long>()
//                        ,"%"+values[0]+"%","%"+values[1]+"%").intValue();
            num =  queryRunner.query("SELECT count(1) FROM audition_info,student_info WHERE student_info.student_name LIKE ? AND student_info.student_id=audition_info.student_id AND audition_info.audition_course like ? ",new ScalarHandler<Long>()
                       ,"%"+values[0]+"%","%"+values[1]+"%").intValue();

        } catch (SQLException throwables) {
            log.error("根据条件获取数据数量出现异常",throwables);

        }
        log.info("返回结果"+num);
        log.info("数据访问层==[AuditionInfoDaoImpl]->getCount(String... values),执行结束");
        return num;
    }

    @Override
    public List<AuditionInfo> pageByValues(int startIndex, int pageSize, String... values) {
        log.info("数据访问层==[AuditionInfoDaoImpl]->pageByValues(int startIndex, int pageSize, String... values),开始执行");
        List<AuditionInfo> list = null;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = queryRunner.query("SELECT * FROM audition_info a,student_info b WHERE b.`student_name` LIKE ? AND b.`student_id`=a.`student_id` AND a.`audition_course` like ? limit ?,?", new BeanListHandler<AuditionInfo>(AuditionInfo.class)
                    , "%" + values[0] + "%", "%" + values[1] + "%", startIndex, pageSize);
        } catch (SQLException throwables) {
            log.error("根据条件获取分页数据出现异常",throwables);

        }
        log.info("返回结果"+list.toString());
        log.info("数据访问层==[AuditionInfoDaoImpl]->pageByValues(int startIndex, int pageSize, String... values),执行结束");
        return list;
    }


}
